package com.lmx.persistence.service;

import java.sql.Date;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.lmx.common.context.ActionContextHolder;
import com.lmx.common.util.CommonUtil;
import com.lmx.repoDomain.tmodel.ConsultInfo;
import com.lmx.repoDomain.tmodel.Customer;
import com.lmx.repoDomain.tmodel.Dicttype;
import com.lmx.repoDomain.tmodel.Employee;
import com.lmx.repoDomain.tmodel.NursingLevel;
import com.lmx.repoDomain.tmodel.StatQuery;
import com.lmx.repoDomain.tmodel.Tenant;

/**
 *
 *
 * @author huibati
 * @version 1.00
 */
@Service
@Scope("prototype")
public class StatService<T> {

	private Log logger = LogFactory.getLog(this.getClass());

	@PersistenceContext
	private EntityManager em;

	@Autowired
	protected GenericService dslJpaService;

	@Autowired
	protected CommonService commonService;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	protected JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * 日查询
	 */
	public List statQueryForDay(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList = new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long, Dicttype> itemMap = new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql = new StringBuilder(
						"select * from "
								+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_day t,tnb_statquery_querytype t2 WHERE "
								+ " t.RepDate >= FROM_UNIXTIME("
								+ beginTime
								+ ", '%Y-%m-%d')"
								+ " AND t.RepDate <= FROM_UNIXTIME("
								+ endTime
								+ ", '%Y-%m-%d')"
								+ " AND t2.stat_type = "
								+ type.getId()
								+ " AND t2.stat_item IN "
								+ items
								+ ") t3"
								+ " LEFT JOIN "
								+ " (select FROM_UNIXTIME(stat_date, '%Y-%m-%d') as statDate, stat_type,stat_item as statItemId,sum(stat_result) as sumResult,sum(passengers) as olderSumResult,"
								+ " tenant_id as tenantId from tnb_statquery "
								+ " where  del_status!=1 "
								+ " and tenant_id ="
								+ tenantId
								+ " and stat_type ="
								+ type.getId()
								+ " and stat_date >="
								+ beginTime
								+ " and stat_date <="
								+ endTime
								+ " and stat_item in "
								+ items
								+ " group by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,stat_type,stat_item,tenant_id "
								+ " order by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,stat_type,stat_item )t1 "
								+ " on  (t1.statDate= t3.RepDate and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					// System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
					StatQuery temp = new StatQuery();
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
					if (rece.get("sumResult") != null) {
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					} else {
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long) CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug("statQueryForDay error " + e);
		}

		return dataList;
	}

	/**
	 * 月查询
	 */
	public List statQueryForMonth(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList = new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long, Dicttype> itemMap = new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql = new StringBuilder(
						"select * from "
								+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_month t,tnb_statquery_querytype t2 WHERE "
								+ " t.RepDate >= FROM_UNIXTIME("
								+ beginTime
								+ ", '%Y-%m-%d')"
								+ " AND t.RepDate <= FROM_UNIXTIME("
								+ endTime
								+ ", '%Y-%m-%d')"
								+ " AND t2.stat_type = "
								+ type.getId()
								+ " AND t2.stat_item IN "
								+ items
								+ ") t3"
								+ " LEFT JOIN "
								+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) as statDate, stat_type,stat_item as statItemId,sum(stat_result) as sumResult,"
								+ " tenant_id as tenantId from tnb_statquery "
								+ " where  del_status!=1 "
								+ " and tenant_id ="
								+ tenantId
								+ " and stat_type ="
								+ type.getId()
								+ " and stat_date >="
								+ beginTime
								+ " and stat_date <="
								+ endTime
								+ " and stat_item in "
								+ items
								+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,stat_type,stat_item,tenant_id "
								+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,stat_type,stat_item )t1 "
								+ " on  (t1.statDate= substring(t3.RepDate,1,7) and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDate") + "---" + rece.get("statItemId")
							+ "---" + rece.get("sumResult") + "---" + rece.get("tenantId"));
					StatQuery temp = new StatQuery();
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
					if (rece.get("sumResult") != null) {
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					} else {
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long) CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug("statQueryForMonth error " + e);
		}

		return dataList;
	}

	/**
	 * 季度查询 quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d'))
	 */
	public List statQueryForQuarter(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList = new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long, Dicttype> itemMap = new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql = new StringBuilder(
						"select * from "
								+ "(SELECT t.RepDate,t.id,t.start_time,t.end_time,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_quarter t,tnb_statquery_querytype t2 WHERE "
								+ " t.start_time >= FROM_UNIXTIME("
								+ beginTime
								+ ", '%Y-%m-%d')"
								+ " AND t.end_time <= FROM_UNIXTIME("
								+ endTime
								+ ", '%Y-%m-%d')"
								+ " AND t2.stat_type = "
								+ type.getId()
								+ " AND t2.stat_item IN "
								+ items
								+ ") t3"
								+ " LEFT JOIN "
								+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDateYear,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) as statDateQ,stat_type,stat_item as statItemId,sum(stat_result) as sumResult,"
								+ " tenant_id as tenantId from tnb_statquery "
								+ " where  del_status!=1 "
								+ " and tenant_id ="
								+ tenantId
								+ " and stat_type ="
								+ type.getId()
								+ " and stat_date >="
								+ beginTime
								+ " and stat_date <="
								+ endTime
								+ " and stat_item in "
								+ items
								+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,stat_type,stat_item,tenant_id"
								+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,stat_type,stat_item) t1 "
								+ " on  (CONCAT(t1.statDateYear,'-',t1.statDateQ)= substring(t3.RepDate,1,6)  and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDateYear") + "-" + rece.get("statDateQ")
							+ "---" + rece.get("statItemId") + "---" + rece.get("sumResult")
							+ "---" + rece.get("tenantId"));
					StatQuery temp = new StatQuery();

					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("start_time")
							+ " 00:00:00"));
					if (rece.get("sumResult") != null) {
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					} else {
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long) CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug("statQueryForQuarter error " + e);
		}
		return dataList;
	}

	/**
	 * 年查询
	 */
	public List statQueryForYear(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList = new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long, Dicttype> itemMap = new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql = new StringBuilder(
						"select * from "
								+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_year t,tnb_statquery_querytype t2 WHERE "
								+ " t.RepDate >= FROM_UNIXTIME("
								+ beginTime
								+ ", '%Y-%m-%d')"
								+ " AND t.RepDate <= FROM_UNIXTIME("
								+ endTime
								+ ", '%Y-%m-%d')"
								+ " AND t2.stat_type = "
								+ type.getId()
								+ " AND t2.stat_item IN "
								+ items
								+ ") t3"
								+ " LEFT JOIN "
								+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDate,stat_type,stat_item as statItemId,sum(stat_result) as sumResult, "
								+ " tenant_id as tenantId from tnb_statquery "
								+ " where  del_status!=1 "
								+ " and tenant_id ="
								+ tenantId
								+ " and stat_type ="
								+ type.getId()
								+ " and stat_date >="
								+ beginTime
								+ " and stat_date <="
								+ endTime
								+ " and stat_item in "
								+ items
								+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,stat_type,stat_item,tenant_id"
								+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,stat_type,stat_item) t1 "
								+ " on  (t1.statDate= substring(t3.RepDate,1,4) and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDate") + "---" + rece.get("statItemId")
							+ "---" + rece.get("sumResult") + "---" + rece.get("tenantId"));
					StatQuery temp = new StatQuery();
					temp.setId(Long.parseLong(rece.get("id").toString()));
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
					if (rece.get("sumResult") != null) {
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					} else {
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long) CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug("statQueryForYear error " + e);
		}
		return dataList;
	}

	/**
	 * 实时查询数据
	 */
	public List statQueryOnTime(String statType, Integer dateTime) {
		List dataListTemp = new ArrayList<>();
		List dataList = new ArrayList<>();

		return dataList;
	}

	/**
	 * 根据服务执行实时统计服务等级数
	 */
	public void createServiceExeStat(Customer customer, Employee employee, Integer statTime,
			Integer endTime, List<Long> customerIdsService) {
		// 老人服务等级
		List<NursingLevel> levels = this.em
				.createQuery(
						"select a.level from NursingProject a where a.delStatus != ?1 and a.customer.id=?2   ")
				.setParameter(1, (short) 1).setParameter(2, customer.getId()).getResultList();
		NursingLevel level = levels == null || levels.size() == 0 ? null : levels.get(0);
		if (level == null) {
			return;
		}
		List<StatQuery> statDatas = this.em
				.createQuery(
						"select a from StatQuery a where a.delStatus != ?1 and a.employee.id=?2 and a.statDate =?3 and a.level.id = ?4 ")
				.setParameter(1, (short) 1).setParameter(2, employee.getId())
				.setParameter(3, statTime).setParameter(4, level.getId()).getResultList();
		StatQuery statData = statDatas == null || statDatas.size() == 0 ? new StatQuery()
				: statDatas.get(0);
		if (statData.getId() == null) {
			logger.debug("testcreateServiceExeStat " + statData.getId());
			statData.setStatDate(statTime);
			statData.setLevel(level);
			Tenant tenant = new Tenant();
			tenant.setId(ActionContextHolder.getContext().getTenantId());
			statData.setTenant(tenant);
			statData.setDelStatus((short) 0);
			statData.setEmployee(employee);
			statData.setStatResult(1);
			statData.setPassengers(1);
			statData.setCreateDateTime(CommonUtil.getCurrTimes());
			dslJpaService.setEntityClass(StatQuery.class);
			dslJpaService.create(statData);
		} else {
			if (customerIdsService.contains(customer.getId())) {

			} else {
				String updateSql = "";
				if (!customerIdsService.contains(customer.getId())) {
					updateSql = "update  StatQuery rp set rp.statResult=rp.statResult+1 where rp.id = ?1  ";
					StringBuilder deleteExe = new StringBuilder(updateSql);
					this.em.createQuery(deleteExe.toString()).setParameter(1, statData.getId())
							.executeUpdate();
				}
			}
		}
		return;
	}

	/**
	 * 日查询 统计护理人员 服务
	 */
	public List statServiceQueryForDay(String staffIds, Integer beginTime, Integer endTime) {
		List dataList = new ArrayList<>();
		List dataListTemp = new ArrayList<>();
		Map<Long, List<StatQuery>> allDataMap = new HashMap<>();// key staffid
		Long tenantId = ActionContextHolder.getContext().getTenantId();
		StringBuilder sql = new StringBuilder(
				"select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t0.id as staffId,t2.id as level_id FROM temp_day t,tnb_nursinglevel t2,tnb_employee t0 WHERE "
						+ " t.RepDate >= FROM_UNIXTIME("
						+ beginTime
						+ ", '%Y-%m-%d')"
						+ " AND t.RepDate <= FROM_UNIXTIME("
						+ endTime
						+ ", '%Y-%m-%d')"
						+ " AND t0.id in "
						+ staffIds
						+ " AND t2.del_status =0 and t2.tenant_id="
						+ tenantId
						+ " and t2.tenant_id=t0.tenant_id ) t3"
						+ " LEFT JOIN "
						+ " (select FROM_UNIXTIME(stat_date, '%Y-%m-%d') as statDate, employee_id,level_id as levelId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery "
						+ " where  del_status!=1 "
						+ " and tenant_id ="
						+ tenantId
						+ " and employee_id in"
						+ staffIds
						+ " and stat_date >="
						+ beginTime
						+ " and stat_date <="
						+ endTime
						+ " group by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,employee_id,level_id,tenant_id "
						+ " order by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,employee_id,level_id )t1 "
						+ " on  (t1.statDate= t3.RepDate and t3.level_id=t1.levelId and t3.staffId=t1.employee_id)  ");
		dataListTemp = getJdbcTemplate().queryForList(sql.toString());
		Iterator it = dataListTemp.iterator();
		// 根据staffid 建立各自数据map
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			// System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
			StatQuery sq = new StatQuery();
			try {
				sq.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (rece.get("sumResult") != null) {
				sq.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
			} else {
				sq.setStatResult(0);
			}
			Employee staff = new Employee();
			staff.setId(Long.parseLong(rece.get("staffId").toString()));
			sq.setEmployee(staff);
			NursingLevel level = new NursingLevel();
			level.setId(Long.parseLong(rece.get("level_id").toString()));
			sq.setLevel(level);
			if (allDataMap.containsKey(staff.getId())) {
				allDataMap.get(staff.getId()).add(sq);
			} else {
				List<StatQuery> dataTemp = new ArrayList<>();
				dataTemp.add(sq);
				allDataMap.put(staff.getId(), dataTemp);
			}
		}
		// 处理各个员工数据
		for (Long staffId : allDataMap.keySet()) {
			Map<Integer, List<StatQuery>> dataInnerMap = new HashMap<>();// key
																			// time
			// 根据时间建立map
			for (StatQuery statQuery : allDataMap.get(staffId)) {
				if (dataInnerMap.containsKey(statQuery.getStatDate())) {
					dataInnerMap.get(statQuery.getStatDate()).add(statQuery);
				} else {
					List<StatQuery> dataTemp = new ArrayList<>();
					dataTemp.add(statQuery);
					dataInnerMap.put(statQuery.getStatDate(), dataTemp);
				}
			}
			for (Integer dataTime : dataInnerMap.keySet()) {
				String dataJson = "";
				Map<Long, Integer> dataSumMap = new HashMap<>();// key levelid
				Integer sumData = 0;// 最小时间间隔内总人数
				Employee staff = new Employee();
				for (StatQuery data : dataInnerMap.get(dataTime)) {
					staff = data.getEmployee();
					sumData += data.getStatResult();
					if (dataSumMap.containsKey(data.getLevel().getId())) {
						Integer sumDataTemp = dataSumMap.get(data.getLevel().getId());
						dataSumMap.put(data.getLevel().getId(), sumDataTemp + data.getStatResult());
					} else {
						dataSumMap.put(data.getLevel().getId(), data.getStatResult());
					}
				}
				for (Long levelId : dataSumMap.keySet()) {
					dataJson += "\"" + levelId.toString() + "\":" + dataSumMap.get(levelId) + ",";
				}
				dataJson = dataJson.length() > 0 ? dataJson.substring(0, dataJson.length() - 1)
						: "";
				dataJson = "{" + dataJson + "}";
				StatQuery sQuery = new StatQuery();
				sQuery.setId((long) CommonUtil.buildRandom(8));
				sQuery.setPassengersGrade(dataJson);
				sQuery.setStatResult(sumData);
				sQuery.setStatDate(dataTime);
				sQuery.setEmployee(staff);
				dataList.add(sQuery);
			}
		}
		return dataList;
	}

	/**
	 * 月查询
	 */
	public List statServiceQueryForMonth(String staffIds, Integer beginTime, Integer endTime) {
		List dataList = new ArrayList<>();
		List dataListTemp = new ArrayList<>();
		Long tenantId = ActionContextHolder.getContext().getTenantId();
		Map<Long, List<StatQuery>> allDataMap = new HashMap<>();// key staffid
		StringBuilder sql = new StringBuilder(
				"select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t0.id as staffId,t2.id as level_id FROM temp_month t,tnb_nursinglevel t2,tnb_employee t0 WHERE "
						+ " t.RepDate >= FROM_UNIXTIME("
						+ beginTime
						+ ", '%Y-%m-%d')"
						+ " AND t.RepDate <= FROM_UNIXTIME("
						+ endTime
						+ ", '%Y-%m-%d')"
						+ " AND t0.id in "
						+ staffIds
						+ " AND t2.del_status =0 and t2.tenant_id="
						+ tenantId
						+ " and t2.tenant_id=t0.tenant_id ) t3"
						+ " LEFT JOIN "
						+ " (select FROM_UNIXTIME(stat_date, '%Y-%m-%d') as statDate, employee_id,level_id as levelId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery "
						+ " where  del_status!=1 "
						+ " and tenant_id ="
						+ tenantId
						+ " and employee_id in "
						+ staffIds
						+ " and stat_date >="
						+ beginTime
						+ " and stat_date <="
						+ endTime
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,employee_id,level_id,tenant_id "
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,employee_id,level_id )t1 "
						+ " on  (t1.statDate= substring(t3.RepDate,1,7) and t3.level_id=t1.levelId and t3.staffId=t1.employee_id)  ");
		dataListTemp = getJdbcTemplate().queryForList(sql.toString());
		Iterator it = dataListTemp.iterator();
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			// System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
			StatQuery sq = new StatQuery();
			try {
				sq.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (rece.get("sumResult") != null) {
				sq.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
			} else {
				sq.setStatResult(0);
			}
			Employee staff = new Employee();
			staff.setId(Long.parseLong(rece.get("staffId").toString()));
			sq.setEmployee(staff);
			NursingLevel level = new NursingLevel();
			level.setId(Long.parseLong(rece.get("level_id").toString()));
			sq.setLevel(level);
			if (allDataMap.containsKey(staff.getId())) {
				allDataMap.get(staff.getId()).add(sq);
			} else {
				List<StatQuery> dataTemp = new ArrayList<>();
				dataTemp.add(sq);
				allDataMap.put(staff.getId(), dataTemp);
			}
		}
		// 处理各个员工数据
		for (Long staffId : allDataMap.keySet()) {
			Map<Integer, List<StatQuery>> dataInnerMap = new HashMap<>();// key
																			// time
			// 根据时间建立map
			for (StatQuery statQuery : allDataMap.get(staffId)) {
				if (dataInnerMap.containsKey(statQuery.getStatDate())) {
					dataInnerMap.get(statQuery.getStatDate()).add(statQuery);
				} else {
					List<StatQuery> dataTemp = new ArrayList<>();
					dataTemp.add(statQuery);
					dataInnerMap.put(statQuery.getStatDate(), dataTemp);
				}
			}
			for (Integer dataTime : dataInnerMap.keySet()) {
				String dataJson = "";
				Map<Long, Integer> dataSumMap = new HashMap<>();// key levelid
				Integer sumData = 0;// 最小时间间隔内总人数
				Employee staff = new Employee();
				for (StatQuery data : dataInnerMap.get(dataTime)) {
					staff = data.getEmployee();
					sumData += data.getStatResult();
					if (dataSumMap.containsKey(data.getLevel().getId())) {
						Integer sumDataTemp = dataSumMap.get(data.getLevel().getId());
						dataSumMap.put(data.getLevel().getId(), sumDataTemp + data.getStatResult());
					} else {
						dataSumMap.put(data.getLevel().getId(), data.getStatResult());
					}
				}
				for (Long levelId : dataSumMap.keySet()) {
					dataJson += "\"" + levelId + "\"" + ":" + dataSumMap.get(levelId) + ",";
				}
				dataJson = dataJson.length() > 0 ? dataJson.substring(0, dataJson.length() - 1)
						: "";
				dataJson = "{" + dataJson + "}";
				StatQuery sQuery = new StatQuery();
				sQuery.setId((long) CommonUtil.buildRandom(8));
				sQuery.setPassengersGrade(dataJson);
				sQuery.setStatResult(sumData);
				sQuery.setEmployee(staff);
				sQuery.setStatDate(dataTime);
				dataList.add(sQuery);
			}
		}
		return dataList;
	}

	/**
	 * 季度查询 quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d'))
	 */
	public List statServiceQueryForQuarter(String staffIds, Integer beginTime, Integer endTime) {
		List dataList = new ArrayList<>();
		List dataListTemp = new ArrayList<>();
		Long tenantId = ActionContextHolder.getContext().getTenantId();
		Map<Long, List<StatQuery>> allDataMap = new HashMap<>();// key staffid
		StringBuilder sql = new StringBuilder(
				"select * from "
						+ "(SELECT t.RepDate,t.id,t.start_time,t.end_time,t2.del_status,t2.tenant_id,t0.id as staffId,t2.id as level_id FROM temp_quarter t,tnb_nursinglevel t2,tnb_employee t0 WHERE "
						+ " t.start_time >= FROM_UNIXTIME("
						+ beginTime
						+ ", '%Y-%m-%d')"
						+ " AND t.end_time <= FROM_UNIXTIME("
						+ endTime
						+ ", '%Y-%m-%d')"
						+ " AND t0.id in "
						+ staffIds
						+ " AND t2.del_status =0 and t2.tenant_id="
						+ tenantId
						+ " and t2.tenant_id=t0.tenant_id ) t3"
						+ " LEFT JOIN "
						+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDateYear,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) as statDateQ, employee_id,level_id as levelId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery "
						+ " where  del_status!=1 "
						+ " and tenant_id ="
						+ tenantId
						+ " and employee_id in "
						+ staffIds
						+ " and stat_date >="
						+ beginTime
						+ " and stat_date <="
						+ endTime
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,employee_id,level_id,tenant_id"
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,employee_id,level_id) t1 "
						+ " on  (CONCAT(t1.statDateYear,'-',t1.statDateQ)= substring(t3.RepDate,1,6)  and t3.level_id=t1.levelId and t3.staffId=t1.employee_id)     ");
		dataListTemp = getJdbcTemplate().queryForList(sql.toString());
		Iterator it = dataListTemp.iterator();
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			System.out.println(rece.get("statDateYear") + "-" + rece.get("statDateQ") + "---"
					+ rece.get("statItemId") + "---" + rece.get("sumResult") + "---"
					+ rece.get("tenantId"));
			StatQuery sq = new StatQuery();
			try {
				sq.setStatDate(CommonUtil.strToIntegerTime(rece.get("start_time") + " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (rece.get("sumResult") != null) {
				sq.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
			} else {
				sq.setStatResult(0);
			}
			Employee staff = new Employee();
			staff.setId(Long.parseLong(rece.get("staffId").toString()));
			sq.setEmployee(staff);
			NursingLevel level = new NursingLevel();
			level.setId(Long.parseLong(rece.get("level_id").toString()));
			sq.setLevel(level);
			if (allDataMap.containsKey(staff.getId())) {
				allDataMap.get(staff.getId()).add(sq);
			} else {
				List<StatQuery> dataTemp = new ArrayList<>();
				dataTemp.add(sq);
				allDataMap.put(staff.getId(), dataTemp);
			}
		}
		// 处理各个员工数据
		for (Long staffId : allDataMap.keySet()) {
			Map<Integer, List<StatQuery>> dataInnerMap = new HashMap<>();// key
																			// time
			// 根据时间建立map
			for (StatQuery statQuery : allDataMap.get(staffId)) {
				if (dataInnerMap.containsKey(statQuery.getStatDate())) {
					dataInnerMap.get(statQuery.getStatDate()).add(statQuery);
				} else {
					List<StatQuery> dataTemp = new ArrayList<>();
					dataTemp.add(statQuery);
					dataInnerMap.put(statQuery.getStatDate(), dataTemp);
				}
			}
			for (Integer dataTime : dataInnerMap.keySet()) {
				String dataJson = "";
				Map<Long, Integer> dataSumMap = new HashMap<>();// key levelid
				Integer sumData = 0;// 最小时间间隔内总人数
				Employee staff = new Employee();
				for (StatQuery data : dataInnerMap.get(dataTime)) {
					staff = data.getEmployee();
					sumData += data.getStatResult();
					if (dataSumMap.containsKey(data.getLevel().getId())) {
						Integer sumDataTemp = dataSumMap.get(data.getLevel().getId());
						dataSumMap.put(data.getLevel().getId(), sumDataTemp + data.getStatResult());
					} else {
						dataSumMap.put(data.getLevel().getId(), data.getStatResult());
					}
				}
				for (Long levelId : dataSumMap.keySet()) {
					dataJson += "\"" + levelId + "\"" + ":" + dataSumMap.get(levelId) + ",";
				}
				dataJson = dataJson.length() > 0 ? dataJson.substring(0, dataJson.length() - 1)
						: "";
				dataJson = "{" + dataJson + "}";
				StatQuery sQuery = new StatQuery();
				sQuery.setId((long) CommonUtil.buildRandom(8));
				sQuery.setPassengersGrade(dataJson);
				sQuery.setStatResult(sumData);
				sQuery.setEmployee(staff);
				sQuery.setStatDate(dataTime);
				dataList.add(sQuery);
			}
		}
		return dataList;
	}

	/**
	 * 年查询
	 */
	public List statServiceQueryForYear(String staffIds, Integer beginTime, Integer endTime) {
		List dataList = new ArrayList<>();
		List dataListTemp = new ArrayList<>();
		Long tenantId = ActionContextHolder.getContext().getTenantId();
		Map<Long, List<StatQuery>> allDataMap = new HashMap<>();// key staffid
		StringBuilder sql = new StringBuilder(
				"select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t0.id as staffId,t2.id as level_id FROM temp_year t,tnb_nursinglevel t2,tnb_employee t0 WHERE "
						+ " t.RepDate >= FROM_UNIXTIME("
						+ beginTime
						+ ", '%Y-%m-%d')"
						+ " AND t.RepDate <= FROM_UNIXTIME("
						+ endTime
						+ ", '%Y-%m-%d')"
						+ " AND t0.id in  "
						+ staffIds
						+ " AND t2.del_status =0 and t2.tenant_id="
						+ tenantId
						+ " and t2.tenant_id=t0.tenant_id ) t3"
						+ " LEFT JOIN "
						+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDate, employee_id,level_id as levelId,sum(stat_result) as sumResult, "
						+ " tenant_id as tenantId from tnb_statquery "
						+ " where  del_status!=1 "
						+ " and tenant_id ="
						+ tenantId
						+ " and employee_id in "
						+ staffIds
						+ " and stat_date >="
						+ beginTime
						+ " and stat_date <="
						+ endTime
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,employee_id,level_id,tenant_id"
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,employee_id,level_id) t1 "
						+ " on  (t1.statDate= substring(t3.RepDate,1,4) and t3.level_id=t1.levelId and t3.staffId=t1.employee_id)  ");
		dataListTemp = getJdbcTemplate().queryForList(sql.toString());
		Iterator it = dataListTemp.iterator();
		while (it.hasNext()) {
			Map rece = (Map) it.next();
			// System.out.println(rece.get("statDate") + "---" +
			// rece.get("statItemId") + "---" + rece.get("sumResult")
			// + "---" + rece.get("tenantId"));
			StatQuery sq = new StatQuery();
			try {
				sq.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") + " 00:00:00"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (rece.get("sumResult") != null) {
				sq.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
			} else {
				sq.setStatResult(0);
			}
			Employee staff = new Employee();
			staff.setId(Long.parseLong(rece.get("staffId").toString()));
			sq.setEmployee(staff);
			NursingLevel level = new NursingLevel();
			level.setId(Long.parseLong(rece.get("level_id").toString()));
			sq.setLevel(level);
			if (allDataMap.containsKey(staff.getId())) {
				allDataMap.get(staff.getId()).add(sq);
			} else {
				List<StatQuery> dataTemp = new ArrayList<>();
				dataTemp.add(sq);
				allDataMap.put(staff.getId(), dataTemp);
			}
		}
		// 处理各个员工数据
		for (Long staffId : allDataMap.keySet()) {
			Map<Integer, List<StatQuery>> dataInnerMap = new HashMap<>();// key
																			// time
			// 根据时间建立map
			for (StatQuery statQuery : allDataMap.get(staffId)) {
				if (dataInnerMap.containsKey(statQuery.getStatDate())) {
					dataInnerMap.get(statQuery.getStatDate()).add(statQuery);
				} else {
					List<StatQuery> dataTemp = new ArrayList<>();
					dataTemp.add(statQuery);
					dataInnerMap.put(statQuery.getStatDate(), dataTemp);
				}
			}
			for (Integer dataTime : dataInnerMap.keySet()) {
				String dataJson = "";
				Map<Long, Integer> dataSumMap = new HashMap<>();// key levelid
				Integer sumData = 0;// 最小时间间隔内总人数
				Employee staff = new Employee();
				for (StatQuery data : dataInnerMap.get(dataTime)) {
					staff = data.getEmployee();
					sumData += data.getStatResult();
					if (dataSumMap.containsKey(data.getLevel().getId())) {
						Integer sumDataTemp = dataSumMap.get(data.getLevel().getId());
						dataSumMap.put(data.getLevel().getId(), sumDataTemp + data.getStatResult());
					} else {
						dataSumMap.put(data.getLevel().getId(), data.getStatResult());
					}
				}
				for (Long levelId : dataSumMap.keySet()) {
					dataJson += "\"" + levelId + "\"" + ":" + dataSumMap.get(levelId) + ",";
				}
				dataJson = dataJson.length() > 0 ? dataJson.substring(0, dataJson.length() - 1)
						: "";
				dataJson = "{" + dataJson + "}";
				StatQuery sQuery = new StatQuery();
				sQuery.setId((long) CommonUtil.buildRandom(8));
				sQuery.setPassengersGrade(dataJson);
				sQuery.setStatResult(sumData);
				sQuery.setEmployee(staff);
				sQuery.setStatDate(dataTime);
				dataList.add(sQuery);
			}
		}
		return dataList;
	}

	/**
	 * 实时统计
	 *
	 * @author huibati
	 * @param 2017年11月7日
	 */
	public StatQuery statOntime(String queryType, Integer beginTime, Integer endTime) {
		StatQuery sQuery = new StatQuery();
		Long tenantId = ActionContextHolder.getContext().getTenantId();
		if ("queryCustomer".equals(queryType)) {
			Integer inNum = 0;
			Integer tryNum = 0;
			Integer nanNum = 0;
			Integer nvNum = 0;
			Integer leaveNum = 0;
			String levelJson = "";
			String customerJson = "";
			Map<String, Integer> ageStat = new HashMap<>();
			ageStat.put("statItem4", 0);
			ageStat.put("statItem5", 0);
			ageStat.put("statItem6", 0);
			ageStat.put("statItem7", 0);
			ageStat.put("statItem8", 0);
			Map<String, Integer> selfCareStat = new HashMap<>();
			String ageJson = "";
			List<Customer> customerList = new ArrayList<>();
			StringBuilder queryCustomer = new StringBuilder();
			queryCustomer
					.append("select cl.customer from CustomerLeave cl where cl.delStatus != 1 and cl.tenant.id =?1 and cl.status=1");
			customerList = this.em.createQuery(queryCustomer.toString()).setParameter(1, tenantId)
					.getResultList();
			leaveNum = customerList.size();

			customerList = new ArrayList<>();
			queryCustomer.delete(0, queryCustomer.length());
			queryCustomer
					.append("select c from Customer c where c.delStatus != 1 and  c.customerStatus is not null"
							+ " and (c.customerStatus.typecode=?1 or c.customerStatus.typecode=?2 )"
							+ " and c.tenant.id =?3 ");
			customerList = this.em.createQuery(queryCustomer.toString())
					.setParameter(1, "customerStatusIn").setParameter(2, "customerStatusTry")
					.setParameter(3, tenantId).getResultList();
			sQuery.setStatDate(customerList.size());
			for (Customer customer : customerList) {
				if ("customerStatusIn".equals(customer.getCustomerStatus().getTypecode())) {
					inNum++;
				}
				if ("customerStatusTry".equals(customer.getCustomerStatus().getTypecode())) {
					tryNum++;
				}
				if ("sexTypeMale".equals(customer.getSex().getTypecode())) {
					nanNum++;
				}
				if ("sexTypeFemale".equals(customer.getSex().getTypecode())) {
					nvNum++;
				}
				if (customer.getAge() != null && customer.getAge() > 0 && customer.getAge() < 70) {
					int ageNum = ageStat.get("statItem4");
					ageStat.put("statItem4", ++ageNum);
				}
				if (customer.getAge() != null && customer.getAge() >= 70 && customer.getAge() <= 79) {
					int ageNum = ageStat.get("statItem5");
					ageStat.put("statItem5", ++ageNum);
				}
				if (customer.getAge() != null && customer.getAge() >= 80 && customer.getAge() <= 89) {
					int ageNum = ageStat.get("statItem6");
					ageStat.put("statItem6", ++ageNum);
				}
				if (customer.getAge() != null && customer.getAge() >= 90 && customer.getAge() <= 99) {
					int ageNum = ageStat.get("statItem7");
					ageStat.put("statItem7", ++ageNum);
				}
				if (customer.getAge() != null && customer.getAge() >= 100) {
					int ageNum = ageStat.get("statItem8");
					ageStat.put("statItem8", ++ageNum);
				}
				if (selfCareStat.containsKey(customer.getSelfCareAbility().getTypecode())) {
					int numTemp = selfCareStat.get(customer.getSelfCareAbility().getTypecode());
					selfCareStat.put(customer.getSelfCareAbility().getTypecode(), ++numTemp);
				} else {
					selfCareStat.put(customer.getSelfCareAbility().getTypecode(), 1);
				}
			}
			customerJson = "\"customerData\":[{" + "\"customerStatusIn\":" + inNum + ","
					+ "\"customerStatusTry\":" + tryNum + ",\"customerLeave\":" + leaveNum
					+ ",\"sexTypeMale\":" + nanNum + ",\"sexTypeFemale\":" + nvNum + "}]";
			if (customerList.size() > 0) {
				// List<NursingLevel> levels = this.em
				// .createQuery(
				// "select a.level from NursingProject a where a.delStatus != ?1 and a.customer in (:ids)   ")
				// .setParameter(1, (short) 1).setParameter("ids",
				// customerList).getResultList();
				// Map<Long, Integer> levelMap = new HashMap<>();
				// for (NursingLevel level : levels) {
				// if (!levelMap.containsKey(level.getId())) {
				// levelMap.put(level.getId(), 1);
				// } else {
				// Integer levelNum = levelMap.get(level.getId());
				// levelMap.put(level.getId(), ++levelNum);
				// }
				// }
				for (String typecode : selfCareStat.keySet()) {
					levelJson += "\"" + typecode + "\"" + ":" + selfCareStat.get(typecode) + ",";
				}
				levelJson = levelJson.length() > 0 ? levelJson.substring(0, levelJson.length() - 1)
						: "";
			}
			levelJson = "\"levelData\":[{" + levelJson + "}]";
			for (String key : ageStat.keySet()) {
				ageJson += "\"" + key + "\":" + ageStat.get(key) + ",";
			}
			ageJson = "\"timeStat\":[{" + ageJson.substring(0, ageJson.length() - 1) + "}]";
			sQuery.setPassengersGrade("{" + customerJson + "," + levelJson + "," + ageJson + "}");
			sQuery.setId((long) CommonUtil.buildRandom(8));
			return sQuery;
		}

		if ("queryCon".equals(queryType)) {
			Integer nanConNum = 0;
			Integer nvConNum = 0;// 咨询人性别
			String conSexJson = "";
			Integer nanConOldNum = 0;
			Integer nvConOldNum = 0;// 咨询 老人性别
			String customerJson = "";
			Integer singleNum = 0;
			Integer moreNum = 0;// 个人 夫妻
			String realationJson = "";
			Map<String, Integer> conTimeStat = new HashMap<>();
			conTimeStat.put("0-8", 0);
			conTimeStat.put("8-10", 0);
			conTimeStat.put("10-12", 0);
			conTimeStat.put("12-14", 0);
			conTimeStat.put("14-16", 0);
			conTimeStat.put("16-18", 0);
			conTimeStat.put("18-20", 0);
			conTimeStat.put("20-24", 0);
			String timeJson = "";
			List<ConsultInfo> infos = new ArrayList<>();
			infos = this.em
					.createQuery(
							"select a from ConsultInfo a where a.delStatus != ?1 and a.tenant.id =?2 and a.advTel is not null  and a.advName is not null and a.createDateTime>=?3 and a.createDateTime<=?4 ")
					.setParameter(1, (short) 1).setParameter(2, tenantId)
					.setParameter(3, beginTime).setParameter(4, endTime).getResultList();
			for (ConsultInfo info : infos) {
				if (info.getAdvGender() != null
						&& "sexTypeMale".equals(info.getAdvGender().getTypecode())) {
					nanConNum++;
				}
				if (info.getAdvGender() != null
						&& "sexTypeFemale".equals(info.getAdvGender().getTypecode())) {
					nvConNum++;
				}
				if (info.getCustomerGender() != null
						&& "sexTypeMale".equals(info.getCustomerGender().getTypecode())) {
					nanConOldNum++;
				}
				if (info.getCustomerGender() != null
						&& "sexTypeFemale".equals(info.getCustomerGender().getTypecode())) {
					nvConOldNum++;
				}
				if (info.getRelationType() == null
						|| (info.getRelationType() != null && "conRelationTypeGR".equals(info
								.getRelationType().getTypecode()))) {
					singleNum++;
				}
				if (info.getRelationType() != null
						&& "conRelationTypeFQ".equals(info.getRelationType().getTypecode())) {
					moreNum++;
				}
				Date conDate = new Date(info.getCreateDateTime() * 1000l);
				int hour = conDate.getHours();
				int min = conDate.getMinutes();
				// 0-8
				if ((hour >= 0 && hour <= 7)) {
					int numTemp = conTimeStat.get("0-8");
					conTimeStat.put("0-8", ++numTemp);
				}
				// 8-10
				if ((hour >= 8 && hour <= 9)) {
					int numTemp = conTimeStat.get("8-10");
					conTimeStat.put("8-10", ++numTemp);
				}
				// 10-12
				if ((hour >= 10 && hour <= 11)) {
					int numTemp = conTimeStat.get("10-12");
					conTimeStat.put("10-12", ++numTemp);
				}
				// 12-14
				if ((hour >= 12 && hour <= 13)) {
					int numTemp = conTimeStat.get("12-14");
					conTimeStat.put("12-14", ++numTemp);
				}
				// 14-16
				if ((hour >= 14 && hour <= 15)) {
					int numTemp = conTimeStat.get("14-16");
					conTimeStat.put("14-16", ++numTemp);
				}
				// 16-18
				if ((hour >= 16 && hour <= 17)) {
					int numTemp = conTimeStat.get("16-18");
					conTimeStat.put("16-18", ++numTemp);
				}
				// 18-20
				if ((hour >= 18 && hour <= 20)) {
					int numTemp = conTimeStat.get("18-20");
					conTimeStat.put("18-20", ++numTemp);
				}
				// 20-24
				if ((hour >= 20 && hour <= 23)) {
					int numTemp = conTimeStat.get("20-24");
					conTimeStat.put("20-24", ++numTemp);
				}
			}
			for (String key : conTimeStat.keySet()) {
				timeJson += "\"" + key + "\":" + conTimeStat.get(key) + ",";
			}
			timeJson = "\"timeStat\":[{" + timeJson.substring(0, timeJson.length() - 1) + "}]";
			conSexJson = "\"conSex\":[{" + "\"sexTypeMale\":" + nanConNum + ",\"sexTypeFemale\":"
					+ nvConNum + "}]";
			customerJson = "\"conOld\":[{" + "\"sexTypeMale\":" + nanConOldNum
					+ ",\"sexTypeFemale\":" + nvConOldNum + "}]";
			realationJson = "\"conRea\":[{" + "\"conRelationTypeGR\":" + singleNum
					+ ",\"conRelationTypeFQ\":" + moreNum + "}]";
			sQuery.setPassengersGrade("{" + conSexJson + "," + customerJson + "," + timeJson + ","
					+ realationJson + "}");
			sQuery.setId((long) CommonUtil.buildRandom(8));
			return sQuery;
		}
		return sQuery;
	}

	/*
	 * public List queryForYear(String statType, Integer beginTime, Integer
	 * endTime) throws ParseException { Dicttype type =
	 * commonService.getDicttype(statType); List<Dicttype> statItems =
	 * commonService.getStatItemByType(statType); Map<Long, Dicttype> itemMap =
	 * new HashMap<>(); for (int i = 0; i < statItems.size(); i++) {
	 * itemMap.put(statItems.get(i).getId(), statItems.get(i)); } List
	 * dataListTemp = new ArrayList<>(); StringBuilder sql = new
	 * StringBuilder("SELECT RepDate,id FROM temp_day t WHERE " +
	 * " RepDate >= FROM_UNIXTIME(" + beginTime + ", '%Y-%m-%d')" +
	 * " AND RepDate <= FROM_UNIXTIME(" + endTime + ", '%Y-%m-%d')");
	 * dataListTemp = getJdbcTemplate().queryForList(sql.toString()); Iterator
	 * it = dataListTemp.iterator(); List<StatQuery> dataList = new
	 * ArrayList<>(); List<StatQuery> dataStatQueries = new ArrayList<>(); if
	 * (statType.equals("statType1")) {
	 * dataStatQueries.addAll(this.statCheckLive(beginTime, endTime));
	 * dataStatQueries.addAll(this.statTryLive(beginTime, endTime));
	 * dataStatQueries.addAll(this.statInRoom(beginTime, endTime)); } else if
	 * (statType.equals("statType12")) { dataStatQueries =
	 * this.statAdvType(beginTime, endTime); } else if
	 * (statType.equals("statType13")) { dataStatQueries =
	 * this.statInPreference(beginTime, endTime); } else if
	 * (statType.equals("statType15")) { dataStatQueries =
	 * this.statCustomerSelfCareAbility(beginTime, endTime); } else if
	 * (statType.equals("statType14")) { dataStatQueries =
	 * this.statLiveIntent(beginTime, endTime); } else if
	 * (statType.equals("statType27")) { dataStatQueries =
	 * this.statRoomType(beginTime, endTime); } else if
	 * (statType.equals("statType27")) { dataStatQueries =
	 * this.statRoomType(beginTime, endTime); } while (it.hasNext()) { Map rece
	 * = (Map) it.next(); StatQuery temp = new StatQuery(); // 类型遍历 for
	 * (Dicttype dt : statItems) {
	 * temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate") +
	 * " 00:00:00")); temp.setStatItemType(dt); for (StatQuery sq :
	 * dataStatQueries) { if
	 * (rece.get("RepDate").toString().equals(sq.getStringDate()) &&
	 * dt.getId().equals(sq.getStatItemType())) {
	 * temp.setStatResult(sq.getStatResult()); } else { temp.setStatResult(0); }
	 * } } temp.setStatType(type); temp.setId((long) CommonUtil.buildRandom(8));
	 * dataList.add(temp); } return dataList; }
	 * 
	 * public List<StatQuery> statRoomType(Integer beginTime, Integer endTime) {
	 * StringBuilder sql = new StringBuilder(); sql.append(
	 * "select a.roomType ,a.tenant ,count(a.roomType),a.advDate  from ConsultInfo a where a.delStatus != ?1 and ( "
	 * +
	 * "a.advDate>=?2 and a.advDate<=?3 ) and a.tenant.id= ?4 and a.roomType is not null group by a.roomType,FROM_UNIXTIME(a.advDate, '%Y-%m-%d')"
	 * ); List roomList = this.em.createQuery(sql.toString()).setParameter(1,
	 * (short) 1) .setParameter(2, beginTime).setParameter(3, endTime)
	 * .setParameter(4,
	 * ActionContextHolder.getContext().getTenantId()).getResultList(); return
	 * this.commonStatQuery(roomList, "statType27"); }
	 *//**
	 *
	 * TODO 试住人数统计
	 *
	 * @author ytche
	 * @param beginTime
	 * @param endTime
	 * @return
	 * @date 2018年4月28日 下午5:52:54
	 */
	/*
	 * public List<StatQuery> statTryLive(Integer beginTime, Integer endTime) {
	 * // 入住---试住人数统计 条件： 试住/入住开始时间不为空 试住时间处于查询范围内; StringBuilder sql = new
	 * StringBuilder(
	 * " select count(*) as sum ,tenant_id,FROM_UNIXTIME(experience_start_time, '%Y-%m-%d') as date  from tnb_customer_businessflow where  del_status!=1  "
	 * +
	 * "  and experience_start_time IS NOT NULL AND checkin_start_time IS NOT  NULL "
	 * + "  and experience_start_time>= " + beginTime +
	 * "  and experience_start_time<= " + endTime +
	 * "  group by FROM_UNIXTIME(experience_start_time, '%Y-%m-%d') "); List
	 * dataTryCount = getJdbcTemplate().queryForList(sql.toString()); Iterator
	 * it = dataTryCount.iterator(); Dicttype type1 =
	 * commonService.getDicttype("statType1"); Dicttype item2 =
	 * commonService.getDicttype("statItem2"); List<StatQuery> dataList = new
	 * ArrayList<>(); while (it.hasNext()) { Map rece = (Map) it.next();
	 * StatQuery data = new StatQuery();
	 * data.setStatDate(Integer.parseInt(rece.get("date").toString()));
	 * data.setStatItemType(item2); Tenant tenant = new Tenant();
	 * tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
	 * data.setTenant(tenant);
	 * data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
	 * data.setCreateDateTime(CommonUtil.getCurrTimes());
	 * data.setStatType(type1); data.setDelStatus((short) 0);
	 * dataList.add(data); } return dataList; }
	 * 
	 * public List<StatQuery> statCheckLive(Integer beginTime, Integer endTime)
	 * { // 入住---入住人数统计 条件：试住时间为空 入住开始时间(最小值)处于查询范围内; StringBuilder sql = new
	 * StringBuilder(); sql.append(
	 * "select count(*) as sum ,tenant_id,FROM_UNIXTIME(checkin_start_time, '%Y-%m-%d') as date from tnb_customer_businessflow where  del_status!=1  "
	 * +
	 * "  and experience_start_time IS  NULL AND checkin_start_time IS NOT NULL "
	 * + "  and checkin_start_time>= " + beginTime +
	 * "  and checkin_start_time<= " + endTime +
	 * " group by FROM_UNIXTIME(checkin_start_time, '%Y-%m-%d') "); List
	 * dataCheckInCount = getJdbcTemplate().queryForList(sql.toString());
	 * Iterator it = dataCheckInCount.iterator(); Dicttype type1 =
	 * commonService.getDicttype("statType1"); Dicttype item1 =
	 * commonService.getDicttype("statItem1"); List<StatQuery> dataList = new
	 * ArrayList<>(); while (it.hasNext()) { Map rece = (Map) it.next();
	 * StatQuery data = new StatQuery();
	 * data.setStatDate(Integer.parseInt(rece.get("date").toString()));
	 * data.setStatItemType(item1); Tenant tenant = new Tenant();
	 * tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
	 * data.setTenant(tenant);
	 * data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
	 * data.setCreateDateTime(CommonUtil.getCurrTimes());
	 * data.setStatType(type1); data.setDelStatus((short) 0);
	 * dataList.add(data); } return dataList; }
	 * 
	 * public List<StatQuery> statInRoom(Integer beginTime, Integer endTime) {
	 * // 入住---试住入住总人数统计 条件：试住/入住开始时间(最小值)处于查询范围内; StringBuilder sql = new
	 * StringBuilder(); sql.append(
	 * "select count(*) as sum ,tenant_id ,FROM_UNIXTIME(checkin_start_time, '%Y-%m-%d') as date from tnb_customer_businessflow where  del_status!=1  "
	 * + "  and checkin_start_time IS NOT NULL " + "  and checkin_start_time>= "
	 * + beginTime + "  and checkin_start_time<= " + endTime +
	 * "  group by FROM_UNIXTIME(checkin_start_time, '%Y-%m-%d') "); List
	 * dataInSumCount = getJdbcTemplate().queryForList(sql.toString()); Iterator
	 * it = dataInSumCount.iterator(); Dicttype item3 =
	 * commonService.getDicttype("statItem3"); Dicttype type1 =
	 * commonService.getDicttype("statType1"); List<StatQuery> dataList = new
	 * ArrayList<>(); while (it.hasNext()) { Map rece = (Map) it.next();
	 * StatQuery data = new StatQuery();
	 * data.setStatDate(Integer.parseInt(rece.get("date").toString()));
	 * data.setStatItemType(item3); Tenant tenant = new Tenant();
	 * tenant.setId(Long.parseLong(rece.get("tenant_id").toString()));
	 * data.setTenant(tenant);
	 * data.setStatResult((Integer.parseInt(rece.get("sum").toString())));
	 * data.setCreateDateTime(CommonUtil.getCurrTimes());
	 * data.setStatType(type1); data.setDelStatus((short) 0);
	 * dataList.add(data); } return dataList; }
	 * 
	 * 
	 * public List<StatQuery> statConsultCount(Integer beginTime, Integer
	 * endTime) { // 咨询---咨询次数 条件：咨询表 咨询时间 StringBuilder sql = new
	 * StringBuilder(); sql.append(
	 * "select a.tenant ,count(a)  from ConsultInfo a where a.delStatus != ?1 and ( "
	 * + "a.advDate>=?2 and a.advDate<=?3 ) group by a.tenant,a.advDate  ");
	 * List dataConsultCount =
	 * this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
	 * .setParameter(2, beginTime).setParameter(3, endTime).getResultList();
	 * return this.commonStatQuery(dataConsultCount, "statType11"); }
	 * 
	 * public List<StatQuery> statAdvType(Integer beginTime, Integer endTime) {
	 * // 咨询---营销渠道 条件：咨询表 咨询时间 //huibati StringBuilder sql = new
	 * StringBuilder(); sql.append(
	 * "select c.advType ,c.info.tenant, count(c.advType),a.advDate from ConsultAdv c ,ConsultInfo a where "
	 * +
	 * " c.info id=a.id and a.delStatus != ?1 and a.advDate>=?2 and a.advDate<=?3 and a.tenant.id= ?4 "
	 * + " group by c.advType,FROM_UNIXTIME(a.advDate, '%Y-%m-%d')"); List
	 * dataConsultadvWay = this.em.createQuery(sql.toString()).setParameter(1,
	 * (short) 1) .setParameter(2, beginTime).setParameter(3, endTime)
	 * .setParameter(4,
	 * ActionContextHolder.getContext().getTenantId()).getResultList(); return
	 * this.commonStatQuery(dataConsultadvWay, "statType12"); }
	 * 
	 * public List<StatQuery> statInPreference(Integer beginTime, Integer
	 * endTime) { // 咨询---入住偏好 条件：咨询表 咨询时间 StringBuilder sql = new
	 * StringBuilder(
	 * "select c.inPreference, c.info.tenant,count(c.inPreference),a.advDate from ConsultInpreference c,ConsultInfo a where "
	 * +
	 * "c.info.id =a.id and a.delStatus != ?1 and a.advDate>=?2 and a.advDate<=?3 and a.tenant.id= ?4 group by c.inPreference,FROM_UNIXTIME(a.advDate, '%Y-%m-%d')"
	 * ); List dataConsultInPreference = this.em.createQuery(sql.toString())
	 * .setParameter(1, (short) 1).setParameter(2, beginTime).setParameter(3,
	 * endTime) .setParameter(4,
	 * ActionContextHolder.getContext().getTenantId()).getResultList(); return
	 * this.commonStatQuery(dataConsultInPreference, "statType13"); }
	 * 
	 * public List<StatQuery> statLiveIntent(Integer beginTime, Integer endTime)
	 * { // 咨询---入住意向 条件：咨询表 咨询时间 StringBuilder sql = new StringBuilder();
	 * sql.append(
	 * "select a.liveIntent  ,a.tenant ,count(a.liveIntent),a.advDate  from ConsultInfo a where a.delStatus != ?1 and ( "
	 * +
	 * "a.advDate>=?2 and a.advDate<=?3 )and a.tenant.id= ?4 group by a.liveIntent,FROM_UNIXTIME(a.advDate, '%Y-%m-%d')"
	 * ); List dataConsultLiveIntent =
	 * this.em.createQuery(sql.toString()).setParameter(1, (short) 1)
	 * .setParameter(2, beginTime).setParameter(3, endTime) .setParameter(4,
	 * ActionContextHolder.getContext().getTenantId()).getResultList(); return
	 * this.commonStatQuery(dataConsultLiveIntent, "statType14"); }
	 * 
	 * public List<StatQuery> statCustomerSelfCareAbility(Integer beginTime,
	 * Integer endTime) { // 咨询---自理能力 StringBuilder sql = new StringBuilder();
	 * sql.append(
	 * " select a.customerSelfCareAbility ,a.tenant,count(a.customerSelfCareAbility),a.advDate from ConsultInfo a  "
	 * +
	 * " where a.delStatus != ?1 and ( a.advDate>=?2 and a.advDate<=?3 and a.tenant.id= ?4 )"
	 * +
	 * " group by  a.customerSelfCareAbility,FROM_UNIXTIME(a.advDate, '%Y-%m-%d') "
	 * ); List dataConsultSelfCareAbility = this.em.createQuery(sql.toString())
	 * .setParameter(1, (short) 1).setParameter(2, beginTime).setParameter(3,
	 * endTime) .setParameter(4,
	 * ActionContextHolder.getContext().getTenantId()).getResultList(); return
	 * this.commonStatQuery(dataConsultSelfCareAbility, "statType15"); }
	 * 
	 * public List<StatQuery> commonStatQuery(List statQueryList, String
	 * statType) { Iterator it = statQueryList.iterator(); List<StatQuery>
	 * dataList = new ArrayList<>(); Dicttype type =
	 * commonService.getDicttype(statType); while (it.hasNext()) { Object[] res
	 * = (Object[]) it.next(); StatQuery data = new StatQuery();
	 * data.setStatItemType((Dicttype) res[0]); data.setTenant((Tenant) res[1]);
	 * data.setStatResult(((Number) res[2]).intValue());
	 * data.setStatDate(((Number) res[3]).intValue());
	 * data.setCreateDateTime(CommonUtil.getCurrTimes());
	 * data.setStatType(type); data.setDelStatus((short) 0); dataList.add(data);
	 * } return dataList; }
	 */
}
